﻿using System;
using System.Data;
using System.Data.SqlServerCe;

namespace PolePositionMobile.Data
{
    /// <summary>
    /// A layer that connects directly to the database. It must be used only through the Logic Layer.
    /// </summary>
    public static class DataLayer
    {
        //Created by: VMCoder
        //Creation date: -
        //Last updated by: VMCoder
        //Last update: 2008.04.15

        #region Query
        /// <summary>
        /// Retrieves the actual filenames of the images assigned to a visit
        /// </summary>
        public static DataTable SelectImagesFiles(Int32 visitId, SqlCeConnection con)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt = new DataTable();
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    IM.ImageId,";
            _Sql += "\n    IM.Filename";
            _Sql += "\nFROM";
            _Sql += "\n    tb_Vendor_Visit_Images AS IM";
            _Sql += "\nWHERE";
            _Sql += "\n    IM.VisitFk = " + visitId.ToString();
            #endregion Query
            _Da = new SqlCeDataAdapter(_Sql, con);
            _Dt.Clear();
            _Da.Fill(_Dt);
            return _Dt;
        }
        /// <summary>
        /// Retrieves the visit data assigned to a visit
        /// </summary>
        public static Int32 SelectDataId(Int32 visitFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeCommand _Cmd;
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    DataId";
            _Sql += "\nFROM";
            _Sql += "\n    [tb_Vendor_Visit_XStructureMappingData]";
            _Sql += "\nWHERE";
            _Sql += "\n    [VisitFk] = @VisitFk";
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            #endregion Parameters
            return (Int32)_Cmd.ExecuteScalar();
        }
        /// <summary>
        /// Retrieves all visits
        /// </summary>
        public static DataTable SelectAllVisits(SqlCeConnection con)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.28
            //Last updated by: VMCoder
            //Last update: 2008.04.28

            string _Sql;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt = new DataTable();
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    VI.VisitId,";
            _Sql += "\n    VI.VisitTypeFk,";
            _Sql += "\n    VI.Latitude,";
            _Sql += "\n    VI.Longitude,";
            _Sql += "\n    VI.Altitude,";
            _Sql += "\n    VI.SatelliteCount,";
            _Sql += "\n    VI.SatelliteTime,";
            _Sql += "\n    VI.SolutionAvailable,";
            _Sql += "\n    VI.TopTypeFk";
            _Sql += "\nFROM";
            _Sql += "\n    tb_Vendor_Visits AS VI";
            #endregion Query
            _Da = new SqlCeDataAdapter(_Sql, con);
            _Dt.Clear();
            _Da.Fill(_Dt);
            return _Dt;
        }
        /// <summary>
        /// Retrieves a complete visit from its id
        /// </summary>
        public static DataTable SelectVisit(Int32 visitId, SqlCeConnection con)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt = new DataTable();
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    VI.VisitId,";
            _Sql += "\n    VI.VisitTypeFk,";
            _Sql += "\n    VI.Latitude,";
            _Sql += "\n    VI.Longitude,";
            _Sql += "\n    VI.Altitude,";
            _Sql += "\n    VI.SatelliteCount,";
            _Sql += "\n    VI.SatelliteTime,";
            _Sql += "\n    VI.SolutionAvailable,";
            _Sql += "\n    VI.TopTypeFk";
            _Sql += "\nFROM";
            _Sql += "\n    tb_Vendor_Visits AS VI";
            _Sql += "\nWHERE";
            _Sql += "\n    VI.VisitId = " + visitId.ToString();
            #endregion Query
            _Da = new SqlCeDataAdapter(_Sql, con);
            _Dt.Clear();
            _Da.Fill(_Dt);
            return _Dt;
        }
        /// <summary>
        /// Retrieves all visit data (supposedly only 1) assigned to a visit
        /// </summary>
        public static DataTable SelectDataXVisit(Int32 visitFk, SqlCeConnection con)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt = new DataTable();
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    DA.DataId";
            _Sql += "\nFROM";
            _Sql += "\n    tb_Vendor_Visit_XStructureMappingData AS DA";
            _Sql += "\nWHERE";
            _Sql += "\n    DA.VisitFk = " + visitFk.ToString();
            #endregion Query
            _Da = new SqlCeDataAdapter(_Sql, con);
            _Dt.Clear();
            _Da.Fill(_Dt);
            return _Dt;
        }
        /// <summary>
        /// Retrieves all poles assigned to a visit data
        /// </summary>
        public static DataTable SelectPolesXData(Int32 dataFk, SqlCeConnection con)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt = new DataTable();
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    PO.PoleId,";
            _Sql += "\n    PO.TopTypeFk,";
            _Sql += "\n    PO.SpeciesFk,";
            _Sql += "\n    PO.TreatmentFk,";
            _Sql += "\n    PO.Year,";
            _Sql += "\n    PO.ClassFk,";
            _Sql += "\n    PO.LVisit,";
            _Sql += "\n    PO.LVisitor,";
            _Sql += "\n    PO.Number,";
            _Sql += "\n    PO.Length,";
            _Sql += "\n    PO.Latitude,";
            _Sql += "\n    PO.Longitude,";
            _Sql += "\n    PO.Altitude,";
            _Sql += "\n    PO.SatelliteCount,";
            _Sql += "\n    PO.SatelliteTime,";
            _Sql += "\n    PO.SolutionAvailable";
            _Sql += "\nFROM";
            _Sql += "\n    tb_Vendor_Visit_XStructureMappingPoles AS PO";
            _Sql += "\nWHERE";
            _Sql += "\n    PO.DataFk = " + dataFk.ToString();
            #endregion Query
            _Da = new SqlCeDataAdapter(_Sql, con);
            _Dt.Clear();
            _Da.Fill(_Dt);
            return _Dt;
        }
        /// <summary>
        /// Retrieves all images assigned to a visit
        /// </summary>
        public static DataTable SelectImagesXVisit(Int32 visitFk, SqlCeConnection con)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt = new DataTable();
            #region Query
            _Sql = "";
            _Sql += "\nSELECT";
            _Sql += "\n    IM.ImageId,";
            _Sql += "\n    IM.PoleFk,";
            _Sql += "\n    IM.Latitude,";
            _Sql += "\n    IM.Longitude,";
            _Sql += "\n    IM.Altitude,";
            _Sql += "\n    IM.SatelliteCount,";
            _Sql += "\n    IM.SatelliteTime,";
            _Sql += "\n    IM.SolutionAvailable,";
            _Sql += "\n    IM.Filename";
            _Sql += "\nFROM";
            _Sql += "\n    tb_Vendor_Visit_Images AS IM";
            _Sql += "\nWHERE";
            _Sql += "\n    IM.VisitFk = " + visitFk;
            #endregion Query

            _Da = new SqlCeDataAdapter(_Sql, con);
            _Dt.Clear();
            _Da.Fill(_Dt);
            return _Dt;
        }
        //THIS WAS COMMENTED BECAUSE SQL SERVER CE DOESN'T SUPPORT COUNT(DISTINCT)
        //public static DataTable GetGridData()
        //{
        //    DataTable _Dt = new DataTable();
        //    SqlCeConnection _Con = null;
        //    SqlCeDataAdapter _Da = null;
        //    string _Sql;
        //    try
        //    {
        //        _Con = Utils.GetConnection();
        //        #region Query
        //        _Sql = "";
        //        _Sql += "\nSELECT";
        //        _Sql += "\n    CONVERT(NVarChar(6), MIN(VI.SatelliteTime), 7) AS Date,";
        //        _Sql += "\n    COUNT(DISTINCT VP.PoleId) AS Poles,";
        //        _Sql += "\n    COUNT(DISTINCT IM.ImageId) AS Images,";
        //        _Sql += "\n    MIN(LU.LookupText) AS Type";
        //        _Sql += "\nFROM";
        //        _Sql += "\n    tb_Vendor_Visits AS VI";
        //        _Sql += "\n    INNER JOIN tb_Vendor_Visit_XStructureMappingData AS VD ON VD.VisitFk = VI.VisitId";
        //        _Sql += "\n    LEFT JOIN tb_Vendor_Visit_XStructureMappingPoles AS VP ON VP.DataFk = VD.DataId";
        //        _Sql += "\n    LEFT JOIN tb_Vendor_Visit_Images AS IM ON IM.VisitFk = VI.VisitId";
        //        _Sql += "\n    LEFT JOIN tb_Lookups AS LU ON VI.VisitTypeFk = LU.LookupValue AND LU.LookupTypeFk = 'VisitTypes'";
        //        _Sql += "\nGROUP BY";
        //        _Sql += "\n    VI.VisitId";
        //        _Sql += "\nORDER BY";
        //        _Sql += "\n    VI.VisitId DESC";
        //        #endregion Query
        //        _Da = new SqlCeDataAdapter(_Sql, _Con);
        //        _Da.SelectCommand.CommandType = CommandType.Text;
        //        _Da.Fill(_Dt);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        if (_Con != null)
        //        {
        //            _Con.Close();
        //        }
        //    }
        //    return _Dt;
        //}
        /// <summary>
        /// Retrieves data to fill the grid in the home page
        /// </summary>
        public static DataTable GetGridData()
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.15

            DataTable _Dt = new DataTable();
            DataTable _DtAux = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    COUNT(IM.ImageId) AS Images";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visits AS VI";
                _Sql += "\n    LEFT JOIN tb_Vendor_Visit_Images AS IM ON IM.VisitFk = VI.VisitId";
                _Sql += "\nGROUP BY";
                _Sql += "\n    VI.VisitId";
                _Sql += "\nORDER BY";
                _Sql += "\n    VI.VisitId DESC";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_DtAux);
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    MIN(VI.VisitId) AS VisitId,";
                _Sql += "\n    MIN(TT.Name) AS TopName,";
                _Sql += "\n    COUNT(VP.PoleId) AS Poles,";
                _Sql += "\n    0 AS Images,";
                //                _Sql += "\n    MIN(VI.SatelliteTime) AS OrderTime,";
                _Sql += "\n    CONVERT(NVarChar(5), MIN(VI.SatelliteTime), 8) AS Time";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visits AS VI";
                _Sql += "\n    INNER JOIN tb_Vendor_Visit_XStructureMappingData AS VD ON VD.VisitFk = VI.VisitId";
                _Sql += "\n    LEFT JOIN tb_Vendor_Visit_XStructureMappingPoles AS VP ON VP.DataFk = VD.DataId";
                _Sql += "\n    LEFT JOIN tb_Tops AS TT ON VI.TopTypeFk = TT.TopId";
                _Sql += "\nGROUP BY";
                _Sql += "\n    VI.VisitId";
                _Sql += "\nORDER BY";
                _Sql += "\n    VI.VisitId DESC";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                    _Dt.Rows[_I]["Images"] = _DtAux.Rows[_I]["Images"];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
            return _Dt;
        }
        /// <summary>
        /// Select lookups of a particular type (usually for dropdown lists)
        /// </summary>
        public static DataTable SelectLookupsXType(string lookupType)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    [LookupId],";
                _Sql += "\n    [LookupValue],";
                _Sql += "\n    [LookupText],";
                _Sql += "\n    [LookupTypeFk]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Lookups]";
                _Sql += "\nWHERE";
                _Sql += "\n    [LookupTypeFk] = '" + lookupType + "'";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
            return _Dt;
        }
        /// <summary>
        /// Select tops for a particular visit type (usually for dropdown lists)
        /// </summary>
        public static DataTable SelectTopsXVisitType(Int32 visitTypeFk)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.10
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    [TopId],";
                _Sql += "\n    [Name]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Tops]";
                _Sql += "\nWHERE";
                _Sql += "\n    [VisitTypeFk] = " + visitTypeFk.ToString();
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
            return _Dt;
        }


        #region Settings
        /// <summary>
        /// Retrieves application settings from database
        /// </summary>
        public static DataTable GetSettings()
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    [SettingId],";
                _Sql += "\n    [SettingKey],";
                _Sql += "\n    [SettingName],";
                _Sql += "\n    [SettingValue]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Settings]";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
            return _Dt;
        }


        #endregion


        public static DataTable GetProjects(Int32 vendorId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT ";
                _Sql += "\n    [ProjectId],";
                _Sql += "\n    [VendorFk],";
                _Sql += "\n    [Name]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Projects]";
                _Sql += "\nWhere (@VendorFk = 0 OR @vendorFk =VendorFk  )";

                #endregion Query

                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@VendorFk", ParameterDirection.Input, false, vendorId));
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static DataTable GetUsers(Int32 vendorId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT ";
                _Sql += "\n    [UserId],";
                _Sql += "\n    [VendorFk],";
                _Sql += "\n    [Name]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Users]";
                _Sql += "\nWhere (@VendorFk = 0 OR @VendorFk = VendorFk )";
                #endregion Query

                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@VendorFk", ParameterDirection.Input, false, vendorId));
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static DataTable GetUtilities()
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT ";
                _Sql += "\n    [UtilityID],";
                _Sql += "\n    [Name]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Utility]";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static DataTable GetVendors()
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            string _Sql;
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _Sql = "";
                _Sql += "\nSELECT ";
                _Sql += "\n    [VendorId],";
                _Sql += "\n    [Name]";
                _Sql += "\nFROM";
                _Sql += "\n    [tb_Vendor]";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (SqlCeException sceex)
            {
                throw sceex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        #endregion

        #region CRUD
        /// <summary>
        /// Inserts a visit in the database
        /// </summary>
        public static Int32 InsertVisit(Int32 visiTypeFk, double latitude, double longitude, double altitude, Int32 satelliteCount, DateTime satelliteTime, bool solutionAvailable, Int32 topTypeFk,Int32 projectFk,Int32 utilityFk,Int32 vendorFk,Int32 userFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nINSERT INTO [tb_Vendor_Visits]";
            _Sql += "\n(";
            _Sql += "\n    [VisitTypeFk],";
            _Sql += "\n    [Latitude],";
            _Sql += "\n    [Longitude],";
            _Sql += "\n    [Altitude],";
            _Sql += "\n    [SatelliteCount],";
            _Sql += "\n    [SatelliteTime],";
            _Sql += "\n    [SolutionAvailable],";
            _Sql += "\n    [ProjectFk],";
            _Sql += "\n    [UtilityFk],";
            _Sql += "\n    [VendorFk],";
            _Sql += "\n    [UserFk],";
            _Sql += "\n    [TopTypeFk]";
            _Sql += "\n)";
            _Sql += "\nVALUES";
            _Sql += "\n(";
            _Sql += "\n    @VisitTypeFk,";
            _Sql += "\n    @Latitude,";
            _Sql += "\n    @Longitude,";
            _Sql += "\n    @Altitude,";
            _Sql += "\n    @SatelliteCount,";
            _Sql += "\n    @SatelliteTime,";
            _Sql += "\n    @SolutionAvailable,";
            _Sql += "\n    @ProjectFk,";
            _Sql += "\n    @UtilityFk,";
            _Sql += "\n    @VendorFk,";
            _Sql += "\n    @UserFk,";
            _Sql += "\n    @TopTypeFk";
            _Sql += "\n)";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, visiTypeFk));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, true, 19, 6, latitude != double.MinValue ? (object)latitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, true, 19, 6, longitude != double.MinValue ? (object)longitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Altitude", ParameterDirection.Input, true, 19, 6, altitude != double.MinValue ? (object)altitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, satelliteCount != Int32.MinValue ? (object)satelliteCount : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime != DateTime.MinValue ? (object)satelliteTime : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvailable));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, topTypeFk != Int32.MinValue ? (object)topTypeFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@ProjectFk", ParameterDirection.Input, true, projectFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@UtilityFk", ParameterDirection.Input, true, utilityFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VendorFk", ParameterDirection.Input, true, vendorFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@UserFk", ParameterDirection.Input, true, userFk));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
            _Cmd = new SqlCeCommand("SELECT MAX(VisitId) AS VisitId FROM [tb_Vendor_Visits]", (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            return (Int32)_Cmd.ExecuteScalar();
        }
        /// <summary>
        /// Updates a visit in the database
        /// </summary>
        public static void UpdateVisit(Int32 visitId, Int32 visitTypeFk, double latitude, double longitude, double altitude, Int32 satelliteCount, DateTime satelliteTime, bool solutionAvailable, Int32 topTypeFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nUPDATE [tb_Vendor_Visits]";
            _Sql += "\nSET";
            _Sql += "\n    [VisitTypeFk] = @VisitTypeFk,";
            _Sql += "\n    [Latitude] = @Latitude,";
            _Sql += "\n    [Longitude] = @Longitude,";
            _Sql += "\n    [Altitude] = @Altitude,";
            _Sql += "\n    [SatelliteCount] = @SatelliteCount,";
            _Sql += "\n    [SatelliteTime] = @SatelliteTime,";
            _Sql += "\n    [SolutionAvailable] = @SolutionAvailable,";
            _Sql += "\n    [TopTypeFk] = @TopTypeFk";
            _Sql += "\nWHERE";
            _Sql += "\n    [VisitId] = @VisitId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitId", ParameterDirection.Input, false, visitId));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, visitTypeFk));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, true, 19, 6, latitude != double.MinValue ? (object)latitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, true, 19, 6, longitude != double.MinValue ? (object)longitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Altitude", ParameterDirection.Input, true, 19, 6, altitude != double.MinValue ? (object)altitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, satelliteCount != Int32.MinValue ? (object)satelliteCount : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime != DateTime.MinValue ? (object)satelliteTime : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvailable));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, topTypeFk != Int32.MinValue ? (object)topTypeFk : (object)DBNull.Value));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Inserts visit data in the database
        /// </summary>
        public static Int32 InsertData(Int32 visitFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nINSERT INTO [tb_Vendor_Visit_XStructureMappingData]";
            _Sql += "\n(";
            _Sql += "\n    VisitFk";
            _Sql += "\n)";
            _Sql += "\nVALUES";
            _Sql += "\n(";
            _Sql += "\n    @VisitFk";
            _Sql += "\n)";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
            _Cmd = new SqlCeCommand("SELECT MAX(DataId) AS DataId FROM [tb_Vendor_Visit_XStructureMappingData]", (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            return (Int32)_Cmd.ExecuteScalar();
        }
        /// <summary>
        /// Updates visit data in the database
        /// </summary>
        public static void UpdateData(Int32 dataId, Int32 visitFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nUPDATE [tb_Vendor_Visit_XStructureMappingData]";
            _Sql += "\nSET";
            _Sql += "\n    VisitFk = @VisitFk";
            _Sql += "\nWHERE";
            _Sql += "\n    DataId = @DataId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@DataId", ParameterDirection.Input, false, dataId));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Inserts a pole in the database
        /// </summary>
        public static Int32 InsertPole(Int32 dataFk, Int32 topTypeFk, Int32 speciesFk, Int32 treatmentFk, Int32 year, Int32 classFk, Int32 lVisit, string lVisitor, string number, double length, double latitude, double longitude, double altitude, Int32 satelliteCount, DateTime satelliteTime, bool solutionAvailable, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nINSERT INTO [tb_Vendor_Visit_XStructureMappingPoles]";
            _Sql += "\n(";
            _Sql += "\n    [DataFk],";
            _Sql += "\n    [TopTypeFk],";
            _Sql += "\n    [SpeciesFk],";
            _Sql += "\n    [TreatmentFk],";
            _Sql += "\n    [Year],";
            _Sql += "\n    [ClassFk],";
            _Sql += "\n    [LVisit],";
            _Sql += "\n    [LVisitor],";
            _Sql += "\n    [Number],";
            _Sql += "\n    [Length],";
            _Sql += "\n    [Latitude],";
            _Sql += "\n    [Longitude],";
            _Sql += "\n    [Altitude],";
            _Sql += "\n    [SatelliteCount],";
            _Sql += "\n    [SatelliteTime],";
            _Sql += "\n    [SolutionAvailable]";
            _Sql += "\n)";
            _Sql += "\nVALUES";
            _Sql += "\n(";
            _Sql += "\n    @DataFk,";
            _Sql += "\n    @TopTypeFk,";
            _Sql += "\n    @SpeciesFk,";
            _Sql += "\n    @TreatmentFk,";
            _Sql += "\n    @Year,";
            _Sql += "\n    @ClassFk,";
            _Sql += "\n    @LVisit,";
            _Sql += "\n    @LVisitor,";
            _Sql += "\n    @Number,";
            _Sql += "\n    @Length,";
            _Sql += "\n    @Latitude,";
            _Sql += "\n    @Longitude,";
            _Sql += "\n    @Altitude,";
            _Sql += "\n    @SatelliteCount,";
            _Sql += "\n    @SatelliteTime,";
            _Sql += "\n    @SolutionAvailable";
            _Sql += "\n)";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@DataFk", ParameterDirection.Input, false, dataFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, topTypeFk != Int32.MinValue ? (object)topTypeFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, true, speciesFk != Int32.MinValue ? (object)speciesFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, treatmentFk != Int32.MinValue ? (object)treatmentFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@Year", ParameterDirection.Input, true, year != Int32.MinValue ? (object)year : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, classFk != Int32.MinValue ? (object)classFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@LVisit", ParameterDirection.Input, true, lVisit != Int32.MinValue ? (object)lVisit : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@LVisitor", 100, ParameterDirection.Input, true, !String.IsNullOrEmpty(lVisitor) ? (object)lVisitor : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@Number", 10, ParameterDirection.Input, true, !String.IsNullOrEmpty(number) ? (object)number : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Length", ParameterDirection.Input, true, 19, 6, length != double.MinValue ? (object)length : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, true, 19, 6, latitude != double.MinValue ? (object)latitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, true, 19, 6, longitude != double.MinValue ? (object)longitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Altitude", ParameterDirection.Input, true, 19, 6, altitude != double.MinValue ? (object)altitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, satelliteCount != Int32.MinValue ? (object)satelliteCount : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime != DateTime.MinValue ? (object)satelliteTime : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvailable));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
            _Cmd = new SqlCeCommand("SELECT MAX(PoleId) AS PoleId FROM [tb_Vendor_Visit_XStructureMappingPoles]", (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            return (Int32)_Cmd.ExecuteScalar();
        }
        /// <summary>
        /// Updates a pole in the database
        /// </summary>
        public static void UpdatePole(Int32 poleId, Int32 dataFk, Int32 topTypeFk, Int32 speciesFk, Int32 treatmentFk, Int32 year, Int32 classFk, Int32 lVisit, string lVisitor, string number, double length, double latitude, double longitude, double altitude, Int32 satelliteCount, DateTime satelliteTime, bool solutionAvailable, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nUPDATE [tb_Vendor_Visit_XStructureMappingPoles]";
            _Sql += "\nSET";
            _Sql += "\n    [DataFk] = @DataFk,";
            _Sql += "\n    [TopTypeFk] = @TopTypeFk,";
            _Sql += "\n    [SpeciesFk] = @SpeciesFk,";
            _Sql += "\n    [TreatmentFk] = @TreatmentFk,";
            _Sql += "\n    [Year] = @Year,";
            _Sql += "\n    [ClassFk] = @ClassFk,";
            _Sql += "\n    [LVisit] = @LVisit,";
            _Sql += "\n    [LVisitor] = @LVisitor,";
            _Sql += "\n    [Number] = @Number,";
            _Sql += "\n    [Length] = @Length,";
            _Sql += "\n    [Latitude] = @Latitude,";
            _Sql += "\n    [Longitude] = @Longitude,";
            _Sql += "\n    [Altitude] = @Altitude,";
            _Sql += "\n    [SatelliteCount] = @SatelliteCount,";
            _Sql += "\n    [SatelliteTime] = @SatelliteTime,";
            _Sql += "\n    [SolutionAvailable] = @SolutionAvailable";
            _Sql += "\nWHERE";
            _Sql += "\n    PoleId = @PoleId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@PoleId", ParameterDirection.Input, false, poleId));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@DataFk", ParameterDirection.Input, false, dataFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, topTypeFk != Int32.MinValue ? (object)topTypeFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, true, speciesFk != Int32.MinValue ? (object)speciesFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, treatmentFk != Int32.MinValue ? (object)treatmentFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@Year", ParameterDirection.Input, true, year != Int32.MinValue ? (object)year : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, classFk != Int32.MinValue ? (object)classFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@LVisit", ParameterDirection.Input, true, lVisit != Int32.MinValue ? (object)lVisit : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@LVisitor", 100, ParameterDirection.Input, true, !String.IsNullOrEmpty(lVisitor) ? (object)lVisitor : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@Number", 10, ParameterDirection.Input, true, !String.IsNullOrEmpty(number) ? (object)number : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Length", ParameterDirection.Input, true, 19, 6, length != double.MinValue ? (object)length : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, true, 19, 6, latitude != double.MinValue ? (object)latitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, true, 19, 6, longitude != double.MinValue ? (object)longitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Altitude", ParameterDirection.Input, true, 19, 6, altitude != double.MinValue ? (object)altitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, satelliteCount != Int32.MinValue ? (object)satelliteCount : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime != DateTime.MinValue ? (object)satelliteTime : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvailable));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Deletes a pole from the database
        /// </summary>
        public static void DeletePole(Int32 poleId, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nDELETE FROM [tb_Vendor_Visit_XStructureMappingPoles]";
            _Sql += "\nWHERE";
            _Sql += "\n    PoleId = @PoleId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@PoleId", ParameterDirection.Input, false, poleId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Inserts an image in the database
        /// </summary>
        public static Int32 InsertImage(Int32 visitFk, Int32 poleFk, double latitude, double longitude, double altitude, Int32 satelliteCount, DateTime satelliteTime, bool solutionAvailable, string filename, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nINSERT INTO [tb_Vendor_Visit_Images]";
            _Sql += "\n(";
            _Sql += "\n    [VisitFk],";
            _Sql += "\n    [PoleFk],";
            _Sql += "\n    [Latitude],";
            _Sql += "\n    [Longitude],";
            _Sql += "\n    [Altitude],";
            _Sql += "\n    [SatelliteCount],";
            _Sql += "\n    [SatelliteTime],";
            _Sql += "\n    [SolutionAvailable],";
            _Sql += "\n    [Filename]";
            _Sql += "\n)";
            _Sql += "\nVALUES";
            _Sql += "\n(";
            _Sql += "\n    @VisitFk,";
            _Sql += "\n    @PoleFk,";
            _Sql += "\n    @Latitude,";
            _Sql += "\n    @Longitude,";
            _Sql += "\n    @Altitude,";
            _Sql += "\n    @SatelliteCount,";
            _Sql += "\n    @SatelliteTime,";
            _Sql += "\n    @SolutionAvailable,";
            _Sql += "\n    @Filename";
            _Sql += "\n)";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@PoleFk", ParameterDirection.Input, true, poleFk != Int32.MinValue ? (object)poleFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, true, 19, 6, latitude != double.MinValue ? (object)latitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, true, 19, 6, longitude != double.MinValue ? (object)longitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Altitude", ParameterDirection.Input, true, 19, 6, altitude != double.MinValue ? (object)altitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, satelliteCount != Int32.MinValue ? (object)satelliteCount : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime != DateTime.MinValue ? (object)satelliteTime : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvailable));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@Filename", 400, ParameterDirection.Input, false, filename));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
            _Cmd = new SqlCeCommand("SELECT MAX(ImageId) AS ImageId FROM [tb_Vendor_Visit_Images]", (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            return (Int32)_Cmd.ExecuteScalar();
        }
        /// <summary>
        /// Updates an image in the database
        /// </summary>
        public static void UpdateImage(Int32 imageId, Int32 visitFk, Int32 poleFk, double latitude, double longitude, double altitude, Int32 satelliteCount, DateTime satelliteTime, bool solutionAvailable, string filename, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nUPDATE [tb_Vendor_Visit_Images]";
            _Sql += "\nSET";
            _Sql += "\n    [VisitFk] = @VisitFk,";
            _Sql += "\n    [PoleFk] = @PoleFk,";
            _Sql += "\n    [Latitude] = @Latitude,";
            _Sql += "\n    [Longitude] = @Longitude,";
            _Sql += "\n    [Altitude] = @Altitude,";
            _Sql += "\n    [SatelliteCount] = @SatelliteCount,";
            _Sql += "\n    [SatelliteTime] = @SatelliteTime,";
            _Sql += "\n    [SolutionAvailable] = @SolutionAvailable,";
            _Sql += "\n    [Filename] = @Filename";
            _Sql += "\nWHERE";
            _Sql += "\n    [ImageId] = @ImageId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@ImageId", ParameterDirection.Input, false, imageId));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@PoleFk", ParameterDirection.Input, true, poleFk != Int32.MinValue ? (object)poleFk : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, true, 19, 6, latitude != double.MinValue ? (object)latitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, true, 19, 6, longitude != double.MinValue ? (object)longitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDecimalParameter("@Altitude", ParameterDirection.Input, true, 19, 6, altitude != double.MinValue ? (object)altitude : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, satelliteCount != Int32.MinValue ? (object)satelliteCount : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime != DateTime.MinValue ? (object)satelliteTime : (object)DBNull.Value));
            _Cmd.Parameters.Add(Utils.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvailable));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@Filename", 400, ParameterDirection.Input, false, filename));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Updates an image's filename in the database
        /// </summary>
        public static void UpdateImageFilename(Int32 imageId, string filename, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nUPDATE [tb_Vendor_Visit_Images]";
            _Sql += "\nSET";
            _Sql += "\n    [Filename] = @Filename";
            _Sql += "\nWHERE";
            _Sql += "\n    [ImageId] = @ImageId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@ImageId", ParameterDirection.Input, false, imageId));
            _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@Filename", 400, ParameterDirection.Input, false, filename));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Deletes an image from the database
        /// </summary>
        public static void DeleteImage(Int32 imageId, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            #region Query
            _Sql = "";
            _Sql += "\nDELETE FROM [tb_Vendor_Visit_Images]";
            _Sql += "\nWHERE";
            _Sql += "\n    [ImageId] = @ImageId";
            #endregion Query
            SqlCeCommand _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@ImageId", ParameterDirection.Input, false, imageId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Deletes all images assigned to a given visit
        /// </summary>
        public static void DeleteImagesXVisit(Int32 visitFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeCommand _Cmd;
            #region Query
            _Sql = "";
            _Sql += "\nDELETE FROM [tb_Vendor_Visit_Images]";
            _Sql += "\nWHERE";
            _Sql += "\n    [VisitFk] = @VisitFk";
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Deletes all poles assigned to a given visit data
        /// </summary>
        public static void DeletePolesXData(Int32 dataFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeCommand _Cmd;
            #region Query
            _Sql = "";
            _Sql += "\nDELETE FROM [tb_Vendor_Visit_XStructureMappingPoles]";
            _Sql += "\nWHERE";
            _Sql += "\n    [DataFk] = @DataFk";
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@DataFk", ParameterDirection.Input, false, dataFk));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Deletes all visit data assigned to a given visit
        /// </summary>
        public static void DeleteDataXVisit(Int32 visitFk, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeCommand _Cmd;
            #region Query
            _Sql = "";
            _Sql += "\nDELETE FROM [tb_Vendor_Visit_XStructureMappingData]";
            _Sql += "\nWHERE";
            _Sql += "\n    [VisitFk] = @VisitFk";
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, visitFk));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Deletes a visit from the database
        /// </summary>
        public static void DeleteVisit(Int32 visitId, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            string _Sql;
            SqlCeCommand _Cmd;
            #region Query
            _Sql = "";
            _Sql += "\nDELETE FROM [tb_Vendor_Visits]";
            _Sql += "\nWHERE";
            _Sql += "\n    [VisitId] = @VisitId";
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@VisitId", ParameterDirection.Input, false, visitId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Saves settings in the database
        /// </summary>
        public static void SaveSettings(Int32 timezone)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            SqlCeTransaction _Tr = null;
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection();
                _Tr = _Con.BeginTransaction();

                string _Sql;
                SqlCeCommand _Cmd;
                #region Timezone
                #region Query
                _Sql = "";
                _Sql += "\nDELETE FROM [tb_Settings]";
                _Sql += "\nWHERE";
                _Sql += "\n    [SettingKey] = @SettingKey";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)_Tr.Connection);
                _Cmd.Transaction = _Tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Timezone));
                #endregion Parameters
                _Cmd.ExecuteNonQuery();
                #region Query
                _Sql = "";
                _Sql += "\nINSERT INTO [tb_Settings]";
                _Sql += "\n(";
                _Sql += "\n    [SettingKey],";
                _Sql += "\n    [SettingName],";
                _Sql += "\n    [SettingValue]";
                _Sql += "\n)";
                _Sql += "\nVALUES";
                _Sql += "\n(";
                _Sql += "\n    @SettingKey,";
                _Sql += "\n    @SettingName,";
                _Sql += "\n    @SettingValue";
                _Sql += "\n)";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)_Tr.Connection);
                _Cmd.Transaction = _Tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Timezone));
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingName", 100, ParameterDirection.Input, false, "Timezone"));
                _Cmd.Parameters.Add(Utils.CreateIntParameter("@SettingValue", ParameterDirection.Input, false, timezone));
                #endregion Parameters
                #endregion
                _Cmd.ExecuteNonQuery();

                _Tr.Commit();
            }
            catch (Exception _Ex)
            {
                if (_Tr != null)
                    _Tr.Rollback();
                throw _Ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void UpdateStructureServerID(Int32 structureServerID, Int32 structureID, string connectionString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Builder = "";
            try
            {
                _MobileConnection = new SqlCeConnection("Data Source = " + connectionString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Builder += "StructureServerID =" + structureID;
                _Builder += ",UploadedDate ='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
                string _Query = "update tb_Vendor_Visits set " + _Builder + " where VisitId =" + structureID + "   ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "update tb_Vendor_Visit_Images set StructureServerID =" + structureServerID + " where VisitFk =" + structureID + "   ";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                _MobileConnection.Close();
            }

        }

        public static void UpdatePoleServerID(Int32 poleSeverID, Int32 poleID, string connectionString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Builder = "";
            try
            {
                SqlCeCommand _SqlCeCommand;
                _MobileConnection = new SqlCeConnection("Data Source = " + connectionString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Builder += "PoleServerID =" + poleSeverID;
                _Builder += ",UploadedDate ='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
                string _Query = "update tb_Vendor_Visit_XStructureMappingPoles set " + _Builder + " where PoleId =" + poleID + "   ";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "update tb_Vendor_Visit_Images set PoleServerID =" + poleSeverID + " where PoleFk =" + poleID + "   ";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                _MobileConnection.Close();
            }

        }

        public static void UpdateImageServerID(Int32 imageServerID, Int32 imageID, string connectionString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Builder = "";
            try
            {
                _MobileConnection = new SqlCeConnection("Data Source = " + connectionString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Builder += "ImageServerID =" + imageServerID;
                _Builder += ",UploadedDate ='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";

                string _Query = "update tb_Vendor_Visit_Images set " + _Builder + " where ImageID =" + imageID + "   ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                _MobileConnection.Close();
            }


        }

        #region Settings 

        public static void SaveSettingsUtility(Int32 utilityid, string name, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10
            try
            {

                string _Sql;
                SqlCeCommand _Cmd;
                #region Timezone
                #region Query
                _Sql = "";
                _Sql += "\nDELETE FROM [tb_Settings]";
                _Sql += "\nWHERE";
                _Sql += "\n    [SettingKey] = @SettingKey";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Utility));
                #endregion Parameters
                _Cmd.ExecuteNonQuery();
                #region Query
                _Sql = "";
                _Sql += "\nINSERT INTO [tb_Settings]";
                _Sql += "\n(";
                _Sql += "\n    [SettingKey],";
                _Sql += "\n    [SettingName],";
                _Sql += "\n    [SettingValue]";
                _Sql += "\n)";
                _Sql += "\nVALUES";
                _Sql += "\n(";
                _Sql += "\n    @SettingKey,";
                _Sql += "\n    @SettingName,";
                _Sql += "\n    @SettingValue";
                _Sql += "\n)";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Utility));
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingName", 100, ParameterDirection.Input, false, name));
                _Cmd.Parameters.Add(Utils.CreateIntParameter("@SettingValue", ParameterDirection.Input, false, utilityid));
                #endregion Parameters
                #endregion
                _Cmd.ExecuteNonQuery();
            }
            catch (Exception _Ex)
            {
                throw _Ex;
            }
        }

        public static void SaveSettingsVendor(Int32 vendorId, string name, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10


            try
            {

                string _Sql;
                SqlCeCommand _Cmd;
                #region Timezone
                #region Query
                _Sql = "";
                _Sql += "\nDELETE FROM [tb_Settings]";
                _Sql += "\nWHERE";
                _Sql += "\n    [SettingKey] = @SettingKey";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Vendor));
                #endregion Parameters
                _Cmd.ExecuteNonQuery();
                #region Query
                _Sql = "";
                _Sql += "\nINSERT INTO [tb_Settings]";
                _Sql += "\n(";
                _Sql += "\n    [SettingKey],";
                _Sql += "\n    [SettingName],";
                _Sql += "\n    [SettingValue]";
                _Sql += "\n)";
                _Sql += "\nVALUES";
                _Sql += "\n(";
                _Sql += "\n    @SettingKey,";
                _Sql += "\n    @SettingName,";
                _Sql += "\n    @SettingValue";
                _Sql += "\n)";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Vendor));
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingName", 100, ParameterDirection.Input, false, name));
                _Cmd.Parameters.Add(Utils.CreateIntParameter("@SettingValue", ParameterDirection.Input, false, vendorId));
                #endregion Parameters
                #endregion
                _Cmd.ExecuteNonQuery();
            }
            catch (Exception _Ex)
            {
                throw _Ex;
            }
        }

        public static void SaveSettingsProject(Int32 projectId, string name, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10
            try
            {

                string _Sql;
                SqlCeCommand _Cmd;
                #region Timezone
                #region Query
                _Sql = "";
                _Sql += "\nDELETE FROM [tb_Settings]";
                _Sql += "\nWHERE";
                _Sql += "\n    [SettingKey] = @SettingKey";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Project));
                #endregion Parameters
                _Cmd.ExecuteNonQuery();
                #region Query
                _Sql = "";
                _Sql += "\nINSERT INTO [tb_Settings]";
                _Sql += "\n(";
                _Sql += "\n    [SettingKey],";
                _Sql += "\n    [SettingName],";
                _Sql += "\n    [SettingValue]";
                _Sql += "\n)";
                _Sql += "\nVALUES";
                _Sql += "\n(";
                _Sql += "\n    @SettingKey,";
                _Sql += "\n    @SettingName,";
                _Sql += "\n    @SettingValue";
                _Sql += "\n)";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.Project));
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingName", 100, ParameterDirection.Input, false, name));
                _Cmd.Parameters.Add(Utils.CreateIntParameter("@SettingValue", ParameterDirection.Input, false, projectId));
                #endregion Parameters
                #endregion
                _Cmd.ExecuteNonQuery();
            }
            catch (Exception _Ex)
            {
                throw _Ex;
            }
        }

        public static void SaveSettingsUser(Int32 userId, string name, SqlCeTransaction tr)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10
            try
            {

                string _Sql;
                SqlCeCommand _Cmd;
                #region Timezone
                #region Query
                _Sql = "";
                _Sql += "\nDELETE FROM [tb_Settings]";
                _Sql += "\nWHERE";
                _Sql += "\n    [SettingKey] = @SettingKey";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.User));
                #endregion Parameters
                _Cmd.ExecuteNonQuery();
                #region Query
                _Sql = "";
                _Sql += "\nINSERT INTO [tb_Settings]";
                _Sql += "\n(";
                _Sql += "\n    [SettingKey],";
                _Sql += "\n    [SettingName],";
                _Sql += "\n    [SettingValue]";
                _Sql += "\n)";
                _Sql += "\nVALUES";
                _Sql += "\n(";
                _Sql += "\n    @SettingKey,";
                _Sql += "\n    @SettingName,";
                _Sql += "\n    @SettingValue";
                _Sql += "\n)";
                #endregion Query
                _Cmd = new SqlCeCommand(_Sql, (SqlCeConnection)tr.Connection);
                _Cmd.Transaction = tr;
                #region Parameters
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingKey", 100, ParameterDirection.Input, false, (Int32)Settings.User));
                _Cmd.Parameters.Add(Utils.CreateNVarCharParameter("@SettingName", 100, ParameterDirection.Input, false, name));
                _Cmd.Parameters.Add(Utils.CreateIntParameter("@SettingValue", ParameterDirection.Input, false, userId));
                #endregion Parameters
                #endregion
                _Cmd.ExecuteNonQuery();
            }
            catch (Exception _Ex)
            {
                throw _Ex;
            }
        }

        #endregion


        #endregion
    }
}
